﻿

--提取集团入职申请/审批列表
CREATE proc [dbo].[proc_Employee_Entry_GetList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@ename nvarchar(50),
	@companyid nvarchar(400),
	@audit nvarchar(4)
)
as

declare @sql nvarchar (4000)
DECLARE @te NVARCHAR(400)
SET @te=''
IF(@companyid<>'0')
SET @te=@te+'e.companyid in ('+@companyid+')'
ELSE
SET @te=@te+'e.id<>0 and e.companyid is not null'
BEGIN				
	set @sql =
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable(nid)'
		+ ' select EE.id from employee_entry EE inner join Employee E on EE.EId=E.Id'
	    + ' where ee.tdepartmentId is not null and '
		+  @te
		+ '	and e.ename like ''%'+@ename+'%'''
		+ ' and (case when ee.audit=1 then 1 '
		+ '		   when ee.audit=0 and ee.auditperson is null then 0 '
		+ ' 	   when ee.audit=0 and ee.auditperson is not null then 2 end) like ''%'+@audit+'%'''	
		+ '	and  e.state <> 5'
		+ ' Order By EE.audit,EE.AuditTime Desc'
		+ ' select' 
		+ ' EE.Id,'
		+ ' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+ ' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+ ' EE.EId,'
		+ ' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+ ' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+ ' (select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,'
		+ ' (select DName from Department where id=EE.TDepartmentId) as DName,'
		+ ' (select Birthday from Employee E where EE.EId=E.Id) as Birthday,'
		+ ' (select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,'
		+ ' (select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,'
		--+ ' (select ZhiWu from Employee E where EE.EId=E.Id) as ZhiWu,'
		+ ' TZhiWu as ZhiWu,'
		+ ' (select EName from Employee E where EE.AuditPerson=E.UserName) as AuditPersonName,'
		+ ' (select COUNT(0) from @indextable) as coun,'
		+ ' EE.Audit,'
		+ ' EE.AuditPerson,'
		+ ' Convert(varchar(10),EE.AuditTime,111) As AuditTime,EE.GID'

		+ ' from employee_entry EE'
		+ '	inner join @indextable t on '
		+ ' EE.id=t.nid	 '
		+ ' where t.id between '+ @startIndex +' and '+ @endIndex 
		+ ' Order By t.id'	
	END

EXEC(@sql)


----提取集团入职申请/审批列表
--ALTER proc [dbo].[proc_Employee_Entry_GetList]
--(
--	@startIndex int,
--	@endIndex int,
--	@docount bit,
--	@ename nvarchar(50),
--	@companyid nvarchar(4),
--	@audit nvarchar(4)
--)
--as

--set nocount on
--	if(@docount=1)
--		select *,'' as EName,'' as Sex,'' as CompanyName,''as DName,''as Birthday,'' as Education,'' as PostName,'' AS AuditPersonName from employee_entry EE inner join Employee E on EE.EId=E.Id
--		where
--			      ee.tdepartmentId is not null
--			and  e.ename like '%'+@ename+'%'
--			and e.companyid like @companyid
--			and (case when ee.audit=1 then '1' 
--				   when ee.audit=0 and ee.auditperson is null then '0'
--				   when ee.audit=0 and ee.auditperson is not null then '1' end) like '%'+@audit+'%'
--			and  e.state <> 5
--	else
--	begin
--	declare @indextable table(id int identity(1,1),nid int)
----	set rowcount @endIndex
--	insert into @indextable(nid) select EE.id from employee_entry EE inner join Employee E on EE.EId=E.Id  
--		where
--			       ee.tdepartmentId is not null
--			and e.ename like '%'+@ename+'%'
--			and e.companyid like @companyid
--			and (case when ee.audit=1 then '1' 
--				   when ee.audit=0 and ee.auditperson is null then '0'
--				   when ee.audit=0 and ee.auditperson is not null then '1' end) like '%'+@audit+'%'
--			and  e.state <> 5
--		order by EE.AuditTime desc

--	select 
--		EE.Id,
--		(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--		(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--		EE.EId,
--		(select EName from Employee E where EE.EId=E.Id) as EName,
--		(select Sex from Employee E where EE.EId=E.Id) as Sex,
--		(select CompanyName from Company where id=(select companyid from employee E where EE.EId=E.Id)) as CompanyName,
--		(select DName from Department where id=EE.TDepartmentId) as DName,
--		(select Birthday from Employee E where EE.EId=E.Id) as Birthday,
--		(select Title from Employee_Education where id=(select Education from employee E where EE.EId=E.Id)) as Education,
--		(select PostName from Post P where id=(select PostId from employee E where EE.EId=E.Id)) as PostName,
--		(select ZhiWu from Employee E where EE.EId=E.Id) as ZhiWu,
--		(select EName from Employee E where EE.AuditPerson=E.UserName) as AuditPersonName,
--		(select COUNT(0) from @indextable) as coun,
--		EE.Audit,
--		EE.AuditPerson,
--		EE.AuditTime
--	from employee_entry EE
--	inner join @indextable t on 
--	EE.id=t.nid
--	where 
--		t.id between @startIndex and @endIndex 
--	order by t.id
--	end
--set nocount off
RETURN